﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using SqlSugar;
using VOL.Core.DbSqlSugar;
using VOL.demo.IRepositories.Outbound;
using VOL.Entity.DomainModels;

namespace VOL.demo.Repositories.Outbound
{
    /// <summary>
    /// 出库单仓储实现
    /// </summary>
    public class OutboundRepository : IOutboundRepository
    {
        private readonly ISqlSugarClient _dbContext;

        public OutboundRepository()
        {
            _dbContext = DbManger.Db; // 使用默认的数据库连接
        }

        /// <summary>
        /// 获取出入库类型下拉列表
        /// </summary>
        /// <returns>出入库类型列表</returns>
        public async Task<List<InOutTypeDto>> GetInOutTypeListAsync()
        {
            try
            {
                var result = await _dbContext.Queryable<inouttypemodel>()
                    .Where(x => x.IsDelete == 0 && x.InOrOut == 1) // 只查询未删除且InOrOut=1的记录
                    .Select(x => new InOutTypeDto
                    {
                        InOutTypeId = x.InOutTypeId,
                        InOutTypeName = x.InOutTypeName
                    })
                    .OrderBy(x => x.InOutTypeId) // 按ID排序
                    .ToListAsync();

                return result;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ OutboundRepository.GetInOutTypeListAsync异常: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw;
            }
        }

        /// <summary>
        /// 获取仓库下拉列表
        /// </summary>
        /// <returns>仓库列表</returns>
        public async Task<List<WarehouseDto>> GetWarehouseListAsync()
        {
            try
            {
                // 先查询总记录数进行调试
                var totalCount = await _dbContext.Queryable<warehousemodel>().CountAsync();
                // 查询未删除的记录数
                var activeCount = await _dbContext.Queryable<warehousemodel>()
                    .Where(x => x.IsDel == 0)
                    .CountAsync();
                // 查询所有记录的IsDel值分布进行调试
                var isDelValues = await _dbContext.Queryable<warehousemodel>()
                    .Select(x => x.IsDel)
                    .ToListAsync();
                var result = await _dbContext.Queryable<warehousemodel>()
                    .Where(x => x.IsDel != 1) // 查询IsDel不等于1的记录（包括null、0等）
                    .Select(x => new WarehouseDto
                    {
                        Id = x.Id,
                        WarehouseName = x.WarehouseName
                    })
                    .OrderBy(x => x.Id) // 按ID排序
                    .ToListAsync();
                return result;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 获取用户下拉列表
        /// </summary>
        /// <returns>用户列表</returns>
        public async Task<List<UserDto>> GetUserListAsync()
        {
            try
            {
                var result = await _dbContext.Queryable<Sys_User>()
                    .Select(x => new UserDto
                    {
                        User_Id = x.User_Id,
                        UserTrueName = x.UserTrueName
                    })
                    .OrderBy(x => x.User_Id) // 按ID排序
                    .ToListAsync();

                return result;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ OutboundRepository.GetUserListAsync异常: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw;
            }
        }

        /// <summary>
        /// 根据出入库类型ID分页查询销售订单（多表联查）
        /// </summary>
        /// <param name="inOutTypeId">出入库类型ID</param>
        /// <param name="pageIndex">页码（从0开始）</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="orderCode">订单编号（可选）</param>
        /// <param name="customerName">客户名称（可选）</param>
        /// <returns>分页结果</returns>
        public async Task<(List<SalesOrderDto> Data, int TotalCount)> GetSalesOrderPageListAsync(
            long inOutTypeId,
            int pageIndex,
            int pageSize,
            string orderCode = null,
            string customerName = null)
        {
            try
            {
                // 检查出入库类型ID，只有16才显示销售订单
                if (inOutTypeId != 16)
                {
                    return (new List<SalesOrderDto>(), 0);
                }
                // 构建查询条件
                var query = _dbContext.Queryable<shopordermodel, customermodel, Sys_Department, Sys_User>(
                    (so, c, d, u) => new JoinQueryInfos(
                        JoinType.Left, so.CustomerId == c.Id,
                        JoinType.Left, so.DeptId == d.DepartmentId.ToString(),
                        JoinType.Left, so.UserId == u.User_Id
                    ))
                    .Where((so, c, d, u) => so.IsDelete == 0); // 只查询未删除的记录

                // 添加订单编号查询条件
                if (!string.IsNullOrEmpty(orderCode))
                {
                    query = query.Where((so, c, d, u) => so.ShopOrderCode.Contains(orderCode));
                }

                // 添加客户名称查询条件
                if (!string.IsNullOrEmpty(customerName))
                {
                    query = query.Where((so, c, d, u) => c.CustomerName.Contains(customerName));
                }

                // 执行分页查询
                RefAsync<int> totalCount = 0;
                var result = await query
                    .OrderByDescending((so, c, d, u) => so.CreateDate) // 按创建时间倒序排列
                    .Select((so, c, d, u) => new SalesOrderDto
                    {
                        Id = so.Id,
                        ShopOrderCode = so.ShopOrderCode,
                        ShopOrderDate = so.ShopOrderDate,
                        CustomerId = so.CustomerId,
                        CustomerName = c.CustomerName ?? "", // 客户名称
                        Contact = so.Contact,
                        ContactPhone = so.ContactPhone,
                        DeptId = so.DeptId.ToString(),
                        DeptName = d.DepartmentName ?? "", // 部门名称
                        UserId = so.UserId,
                        UserName = u.UserTrueName ?? "", // 负责人姓名
                        Status = so.Status,
                        Creator = so.Creator,
                        ShopAllNum = so.ShopAllNum,
                        ShopAllMoney = so.ShopAllMoney,
                        ShopAllGoods = so.ShopAllGoods,
                        CreateDate = so.CreateDate,
                        Remark = so.Remark
                    })
                    .ToPageListAsync(pageIndex, pageSize, totalCount);

                return (result, totalCount.Value);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ OutboundRepository.GetSalesOrderPageListAsync异常: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw;
            }
        }

        /// <summary>
        /// 根据出入库类型ID分页查询进货退货（多表联查）
        /// </summary>
        /// <param name="inOutTypeId">出入库类型ID</param>
        /// <param name="pageIndex">页码（从0开始）</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="returnOrderCode">退货单号（可选）</param>
        /// <param name="supplierName">供应商名称（可选）</param>
        /// <returns>分页结果</returns>
        public async Task<(List<ReturnOrderDto> Data, int TotalCount)> GetReturnOrderPageListAsync(
            long inOutTypeId,
            int pageIndex,
            int pageSize,
            string returnOrderCode = null,
            string supplierName = null)
        {
            try
            {
                // 检查出入库类型ID，只有14才显示进货退货
                if (inOutTypeId != 14)
                {
                    return (new List<ReturnOrderDto>(), 0);
                }
                // 构建查询条件
                var query = _dbContext.Queryable<returnordermodel, suppliermodel, Sys_Department>(
                    (ro, s, d) => new JoinQueryInfos(
                        JoinType.Left, ro.SupplierId == s.Id,
                        JoinType.Left, ro.DeptId == d.DepartmentId.ToString()
                    ))
                    .Where((ro, s, d) => ro.IsDelete == 0); // 只查询未删除的记录

                // 添加退货单号查询条件
                if (!string.IsNullOrEmpty(returnOrderCode))
                {
                    query = query.Where((ro, s, d) => ro.ReturnOrderCode.Contains(returnOrderCode));
                }

                // 添加供应商名称查询条件
                if (!string.IsNullOrEmpty(supplierName))
                {
                    query = query.Where((ro, s, d) => s.SupplierName.Contains(supplierName));
                }

                // 执行分页查询
                RefAsync<int> totalCount = 0;
                var result = await query
                    .OrderByDescending((ro, s, d) => ro.CreateDate) // 按创建时间倒序排列
                    .Select((ro, s, d) => new ReturnOrderDto
                    {
                        Id = ro.Id,
                        ReturnOrderCode = ro.ReturnOrderCode,
                        ReturnOrderDate = ro.ReturnOrderDate,
                        PurchaseOrderCode = ro.PurchaseOrderCode,
                        SupplierId = ro.SupplierId,
                        SupplierName = s.SupplierName ?? "", // 供应商名称
                        Contact = ro.Contact,
                        ContactPhone = ro.ContactPhone,
                        DeptId = ro.DeptId.ToString(),
                        DeptName = d.DepartmentName ?? "", // 部门名称
                        Status =(int) ro.Status,
                        Creator = ro.Creator,
                        CreateDate = ro.CreateDate,
                        Remark = ro.Remarks
                    })
                    .ToPageListAsync(pageIndex, pageSize, totalCount);

                return (result, totalCount.Value);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ OutboundRepository.GetReturnOrderPageListAsync异常: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw;
            }
        }

        /// <summary>
        /// 根据销售订单ID查询销售订单明细（与货品表联查）
        /// </summary>
        /// <param name="shopOrderId">销售订单ID</param>
        /// <returns>销售订单明细列表</returns>
        public async Task<List<SalesOrderDetailDto>> GetSalesOrderDetailsByIdAsync(long shopOrderId)
        {
            try
            {
                var result = await _dbContext.Queryable<shoporderdetailsmodel, productinformationmodel>(
                    (sod, p) => new JoinQueryInfos(
                        JoinType.Left, sod.GoodsId == p.Id
                    ))
                    .Where((sod, p) => sod.ShopOrderId == shopOrderId && sod.IsDelete == 0) // 根据销售订单ID查询未删除的明细
                    .OrderBy((sod, p) => sod.Id) // 按明细ID排序
                    .Select((sod, p) => new SalesOrderDetailDto
                    {
                        Id = sod.Id,
                        ShopOrderId = sod.ShopOrderId,
                        GoodsId = sod.GoodsId,
                        GoodsCode = p.goodsCode ?? "", // 货品编号
                        GoodsName = p.goodsName ?? "", // 货品名称
                        SpecModel = p.specModel ?? "", // 规格型号
                        currentInventory = p.currentInventory, // 当前库存
                        ShopOrderNum = sod.ShopOrderNum,
                        ShopOrderMoney = sod.ShopOrderMoney,
                        ShopOrderAllMoney = sod.ShopOrderAllMoney,
                        Status = p.status, // 货品状态
                        ProductTypeId = p.productTypeId, // 货品类型ID
                        SupplierId = p.supplierId, // 供应商ID
                        Remark = sod.Remark,
                        CreateDate = sod.CreateDate
                    })
                    .ToListAsync();

                return result;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ OutboundRepository.GetSalesOrderDetailsByIdAsync异常: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw;
            }
        }

        /// <summary>
        /// 根据退货订单ID查询退货订单明细（与进货明细表和货品表联查）
        /// </summary>
        /// <param name="returnOrderId">退货订单ID</param>
        /// <returns>退货订单明细列表</returns>
        public async Task<List<ReturnOrderDetailDto>> GetReturnOrderDetailsByIdAsync(long returnOrderId)
        {
            try
            {
                var result = await _dbContext.Queryable<returnorderdetailsmodel, PurchaseOrderItemModel, productinformationmodel>(
                    (rod, poi, p) => new JoinQueryInfos(
                        JoinType.Left, rod.PurchaseOrderItemId == poi.Id,
                        JoinType.Left, poi.GoodsId == p.Id
                    ))
                    .Where((rod, poi, p) => rod.ReturnOrderId == returnOrderId && rod.IsDelete == 0) // 根据退货订单ID查询未删除的明细
                    .OrderBy((rod, poi, p) => rod.Id) // 按明细ID排序
                    .Select((rod, poi, p) => new ReturnOrderDetailDto
                    {
                        Id = rod.Id,
                        ReturnOrderId = rod.ReturnOrderId,
                        PurchaseOrderItemId = rod.PurchaseOrderItemId,
                        GoodsId = poi.GoodsId, // 来自进货明细
                        GoodsCode = p.goodsCode ?? "", // 货品编号（来自货品表）
                        GoodsName = p.goodsName ?? "", // 货品名称（来自货品表）
                        SpecModel = p.specModel ?? "", // 规格型号（来自货品表）
                        currentInventory = p.currentInventory, // 当前库存
                        ReturnNumber = rod.ReturnNumber,
                        ReturnMoney = rod.ReturnMoney,
                        PurchasePrice = poi.PurchasePrice, // 进货单价（来自进货明细）
                        Status = p.status, // 货品状态（来自货品表）
                        ProductTypeId = p.productTypeId, // 货品类型ID（来自货品表）
                        SupplierId = p.supplierId, // 供应商ID（来自货品表）
                        Remarks = rod.Remarks,
                        CreateDate = rod.CreateDate
                    })
                    .ToListAsync();

                return result;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ OutboundRepository.GetReturnOrderDetailsByIdAsync异常: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw;
            }
        }

        /// <summary>
        /// 退货出库（事务性操作）
        /// </summary>
        /// <param name="request">退货出库请求</param>
        /// <returns>出库单ID</returns>
        public async Task<long> CreateReturnOutboundAsync(ReturnOutboundRequestDto request)
        {
            try
            {
                Console.WriteLine($"=== 开始退货出库事务操作 ===");
                Console.WriteLine($"退货订单ID: {request.RelationalID}, 出库明细数量: {request.Details?.Count ?? 0}");

                // 开启事务
                long finalOutboundId = 0;
                await _dbContext.Ado.UseTranAsync(async () =>
                {
                    long outboundId = 0;
                    // 1. 创建出库单主表记录
                    var outbound = new outboundmodel
                    {
                        OutboundCode = request.OutboundCode ?? "",
                        OutInTypeId = request.OutInTypeId,
                        WarehouseId = request.WarehouseId,
                        OutboundDate = request.OutboundDate,
                        RelationalID = request.RelationalID,
                        Contact = request.Contact ?? "",
                        Consignee = request.Consignee ?? "",
                        ContactPhone = request.ContactPhone ?? "",
                        UserId = request.UserId,
                        Remark = request.Remark ?? "",
                        Status = 0, // 初始状态为0（未审核）
                        OutboundAllNum = request.OutboundAllNum,
                        OutboundAllMoney = request.OutboundAllMoney,
                        OutboundAllGoods = request.OutboundAllGoods ?? "",
                        CreateDate = DateTime.Now,
                        Creator = "System", // 可以从当前用户上下文获取
                        IsDelete = 0
                    };

                    // 插入出库单并获取ID
                    Console.WriteLine($"🔄 准备插入出库单，出库编号: {outbound.OutboundCode}");
                    Console.WriteLine($"📋 出库单详细信息:");
                    Console.WriteLine($"   - OutboundCode: {outbound.OutboundCode}");
                    Console.WriteLine($"   - OutInTypeId: {outbound.OutInTypeId}");
                    Console.WriteLine($"   - WarehouseId: {outbound.WarehouseId}");
                    Console.WriteLine($"   - RelationalID: {outbound.RelationalID}");
                    Console.WriteLine($"   - Contact: {outbound.Contact}");
                    Console.WriteLine($"   - Consignee: {outbound.Consignee}");
                    Console.WriteLine($"   - ContactPhone: {outbound.ContactPhone}");
                    Console.WriteLine($"   - UserId: {outbound.UserId}");
                    Console.WriteLine($"   - Remark: {outbound.Remark}");
                    Console.WriteLine($"   - Status: {outbound.Status}");
                    Console.WriteLine($"   - OutboundAllNum: {outbound.OutboundAllNum}");
                    Console.WriteLine($"   - OutboundAllMoney: {outbound.OutboundAllMoney}");
                    Console.WriteLine($"   - OutboundAllGoods: {outbound.OutboundAllGoods}");
                    
                    try
                    {
                        // 方案2：手动生成ID（如果数据库表没有自增长）
                        // 获取当前最大ID
                        var maxId = await _dbContext.Queryable<outboundmodel>().MaxAsync(x => x.Id);
                        outboundId = maxId + 1;
                        outbound.Id = outboundId;
                        
                        Console.WriteLine($"🔢 手动生成出库单ID: {outboundId}");
                        
                        // 使用普通插入而不是返回自增ID
                        var insertResult = await _dbContext.Insertable(outbound).ExecuteCommandAsync();
                        
                        if (insertResult <= 0)
                        {
                            throw new Exception($"插入出库单失败，影响行数: {insertResult}");
                        }
                        
                        Console.WriteLine($"✅ 出库单创建成功，ID: {outboundId}");
                    }
                    catch (Exception insertEx)
                    {
                        Console.WriteLine($"❌ 插入出库单异常: {insertEx.Message}");
                        Console.WriteLine($"异常详情: {insertEx.StackTrace}");
                        throw new Exception($"插入出库单失败: {insertEx.Message}", insertEx);
                    }
                    
                    if (outboundId <= 0)
                    {
                        throw new Exception($"出库单插入失败，返回的ID无效: {outboundId}");
                    }

                    // 2. 批量创建出库明细记录
                    Console.WriteLine($"🔄 开始创建出库明细，明细数量: {request.Details.Count}");
                    var outboundDetails = new List<outbounddetailmodel>();
                    
                    // 获取当前出库明细表的最大ID
                    var maxDetailId = await _dbContext.Queryable<outbounddetailmodel>().MaxAsync(x => x.Id);
                    long currentDetailId = maxDetailId;
                    
                    foreach (var detail in request.Details)
                    {
                        currentDetailId++; // 手动生成明细ID
                        var outboundDetail = new outbounddetailmodel
                        {
                            Id = currentDetailId, // 手动设置ID
                            OutboundId = outboundId,
                            DetialId = detail.DetailId,
                            StorageId = detail.StorageId,
                            OneOutboundNum = detail.OneOutboundNum,
                            BatchNumber = detail.BatchNumber ?? "",
                            ProductionDate = detail.ProductionDate,
                            Remark = detail.Remark ?? "",
                            CreateDate = DateTime.Now,
                            Creator = "System",
                            IsDelete = 0
                        };
                        outboundDetails.Add(outboundDetail);
                        Console.WriteLine($"   📝 准备明细: ID={currentDetailId}, DetailId={detail.DetailId}, StorageId={detail.StorageId}, OutboundNum={detail.OneOutboundNum}");
                    }

                    // 批量插入出库明细
                    Console.WriteLine($"🔄 开始批量插入出库明细...");
                    try
                    {
                        var insertDetailsResult = await _dbContext.Insertable(outboundDetails).ExecuteCommandAsync();
                        Console.WriteLine($"✅ 出库明细创建成功，数量: {insertDetailsResult}");
                    }
                    catch (Exception detailEx)
                    {
                        Console.WriteLine($"❌ 插入出库明细异常: {detailEx.Message}");
                        Console.WriteLine($"异常详情: {detailEx.StackTrace}");
                        throw new Exception($"插入出库明细失败: {detailEx.Message}", detailEx);
                    }

                    // 3. 更新货品表的当前库存（减少库存）
                    // 需要通过退货明细ID找到对应的货品ID
                    foreach (var detail in request.Details)
                    {
                        // 先查询退货明细，获取对应的进货明细ID
                        Console.WriteLine($"🔍 查询退货明细，DetailId: {detail.DetailId}");
                        var returnDetail = await _dbContext.Queryable<returnorderdetailsmodel>()
                            .Where(r => r.Id == detail.DetailId)
                            .FirstAsync();

                        if (returnDetail == null)
                        {
                            throw new Exception($"未找到退货明细，明细ID: {detail.DetailId}");
                        }
                        Console.WriteLine($"✅ 找到退货明细，PurchaseOrderItemId: {returnDetail.PurchaseOrderItemId}");

                        // 再查询进货明细，获取对应的货品ID
                        Console.WriteLine($"🔍 查询进货明细，PurchaseOrderItemId: {returnDetail.PurchaseOrderItemId}");
                        var purchaseDetail = await _dbContext.Queryable<PurchaseOrderItemModel>()
                            .Where(p => p.Id == returnDetail.PurchaseOrderItemId)
                            .FirstAsync();

                        if (purchaseDetail == null)
                        {
                            throw new Exception($"未找到进货明细，进货明细ID: {returnDetail.PurchaseOrderItemId}");
                        }
                        Console.WriteLine($"✅ 找到进货明细，GoodsId: {purchaseDetail.GoodsId}");

                        // 查询当前库存
                        var currentProduct = await _dbContext.Queryable<productinformationmodel>()
                            .Where(p => p.Id == purchaseDetail.GoodsId)
                            .FirstAsync();
                        
                        if (currentProduct == null)
                        {
                            throw new Exception($"未找到货品信息，货品ID: {purchaseDetail.GoodsId}");
                        }
                        Console.WriteLine($"📦 当前库存信息 - 货品ID: {purchaseDetail.GoodsId}, 当前库存: {currentProduct.currentInventory}, 准备减少: {detail.OneOutboundNum}");

                        // 使用进货明细中的货品ID更新库存
                        var updateResult = await _dbContext.Updateable<productinformationmodel>()
                            .SetColumns(p => p.currentInventory == p.currentInventory - detail.OneOutboundNum)
                            .Where(p => p.Id == purchaseDetail.GoodsId)
                            .ExecuteCommandAsync();

                        if (updateResult <= 0)
                        {
                            throw new Exception($"更新货品库存失败，货品ID: {purchaseDetail.GoodsId}，影响行数: {updateResult}");
                        }
                        Console.WriteLine($"✅ 货品库存更新成功，货品ID: {purchaseDetail.GoodsId}, 减少数量: {detail.OneOutboundNum}, 影响行数: {updateResult}");
                    }

                    // 4. 更新退货明细表的退货数量（减少可退货数量）
                    foreach (var detail in request.Details)
                    {
                        // 查询当前退货明细信息
                        var currentReturn = await _dbContext.Queryable<returnorderdetailsmodel>()
                            .Where(r => r.Id == detail.DetailId)
                            .FirstAsync();
                        
                        if (currentReturn == null)
                        {
                            throw new Exception($"未找到退货明细，明细ID: {detail.DetailId}");
                        }
                        Console.WriteLine($"📋 当前退货明细信息 - 明细ID: {detail.DetailId}, 当前可退货数量: {currentReturn.ReturnNumber}, 准备减少: {detail.OneOutboundNum}");

                        var updateResult = await _dbContext.Updateable<returnorderdetailsmodel>()
                            .SetColumns(r => r.ReturnNumber == r.ReturnNumber - detail.OneOutboundNum)
                            .Where(r => r.Id == detail.DetailId)
                            .ExecuteCommandAsync();

                        if (updateResult <= 0)
                        {
                            throw new Exception($"更新退货明细失败，明细ID: {detail.DetailId}，影响行数: {updateResult}");
                        }
                        Console.WriteLine($"✅ 退货明细更新成功，明细ID: {detail.DetailId}, 减少数量: {detail.OneOutboundNum}, 影响行数: {updateResult}");
                    }

                    Console.WriteLine("✅ 退货出库事务操作全部完成");
                    finalOutboundId = outboundId; // 将ID赋值给外部变量
                });

                Console.WriteLine($"✅ 事务提交成功，出库单ID: {finalOutboundId}");
                return finalOutboundId;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 退货出库事务操作失败: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw;
            }
        }

        /// <summary>
        /// 销售订单出库（事务性操作）
        /// </summary>
        /// <param name="request">销售订单出库请求</param>
        /// <returns>出库单ID</returns>
        public async Task<long> CreateSalesOutboundAsync(SalesOutboundRequestDto request)
        {
            try
            {
                Console.WriteLine($"=== 开始销售订单出库事务操作 ===");
                Console.WriteLine($"销售订单ID: {request.RelationalID}, 出库明细数量: {request.Details?.Count ?? 0}");

                // 开启事务
                long finalOutboundId = 0;
                await _dbContext.Ado.UseTranAsync(async () =>
                {
                    long outboundId = 0;
                    // 1. 创建出库单主表记录
                    var outbound = new outboundmodel
                    {
                        OutboundCode = request.OutboundCode ?? "",
                        OutInTypeId = request.OutInTypeId,
                        WarehouseId = request.WarehouseId,
                        OutboundDate = request.OutboundDate,
                        RelationalID = request.RelationalID,
                        Contact = request.Contact ?? "",
                        Consignee = request.Consignee ?? "",
                        ContactPhone = request.ContactPhone ?? "",
                        UserId = request.UserId,
                        Remark = request.Remark ?? "",
                        Status = 0, // 初始状态为0（未审核）
                        OutboundAllNum = request.OutboundAllNum,
                        OutboundAllMoney = request.OutboundAllMoney,
                        OutboundAllGoods = request.OutboundAllGoods ?? "",
                        CreateDate = DateTime.Now,
                        Creator = "System", // 可以从当前用户上下文获取
                        IsDelete = 0
                    };

                    // 插入出库单并获取ID
                    Console.WriteLine($"🔄 准备插入销售出库单，出库编号: {outbound.OutboundCode}");
                    Console.WriteLine($"📋 出库单详细信息:");
                    Console.WriteLine($"   - OutboundCode: {outbound.OutboundCode}");
                    Console.WriteLine($"   - OutInTypeId: {outbound.OutInTypeId}");
                    Console.WriteLine($"   - WarehouseId: {outbound.WarehouseId}");
                    Console.WriteLine($"   - RelationalID: {outbound.RelationalID}");
                    Console.WriteLine($"   - Contact: {outbound.Contact}");
                    Console.WriteLine($"   - UserId: {outbound.UserId}");
                    
                    try
                    {
                        // 手动生成ID（如果数据库表没有自增长）
                        var maxId = await _dbContext.Queryable<outboundmodel>().MaxAsync(x => x.Id);
                        outboundId = maxId + 1;
                        outbound.Id = outboundId;
                        
                        Console.WriteLine($"🔢 手动生成销售出库单ID: {outboundId}");
                        
                        // 使用普通插入而不是返回自增ID
                        var insertResult = await _dbContext.Insertable(outbound).ExecuteCommandAsync();
                        
                        if (insertResult <= 0)
                        {
                            throw new Exception($"插入销售出库单失败，影响行数: {insertResult}");
                        }
                        
                        Console.WriteLine($"✅ 销售出库单创建成功，ID: {outboundId}");
                    }
                    catch (Exception insertEx)
                    {
                        Console.WriteLine($"❌ 插入销售出库单异常: {insertEx.Message}");
                        Console.WriteLine($"异常详情: {insertEx.StackTrace}");
                        throw new Exception($"插入销售出库单失败: {insertEx.Message}", insertEx);
                    }
                    
                    if (outboundId <= 0)
                    {
                        throw new Exception($"销售出库单插入失败，返回的ID无效: {outboundId}");
                    }

                    // 2. 批量创建出库明细记录
                    Console.WriteLine($"🔄 开始创建销售出库明细，明细数量: {request.Details.Count}");
                    var outboundDetails = new List<outbounddetailmodel>();
                    
                    // 获取当前出库明细表的最大ID
                    var maxDetailId = await _dbContext.Queryable<outbounddetailmodel>().MaxAsync(x => x.Id);
                    long currentDetailId = maxDetailId;
                    
                    foreach (var detail in request.Details)
                    {
                        currentDetailId++; // 手动生成明细ID
                        var outboundDetail = new outbounddetailmodel
                        {
                            Id = currentDetailId, // 手动设置ID
                            OutboundId = outboundId,
                            DetialId = detail.DetailId, // 注意：这里使用DetialId（根据您的修改）
                            StorageId = detail.StorageId,
                            OneOutboundNum = detail.OneOutboundNum,
                            BatchNumber = detail.BatchNumber ?? "",
                            ProductionDate = detail.ProductionDate,
                            Remark = detail.Remark ?? "",
                            CreateDate = DateTime.Now,
                            Creator = "System",
                            IsDelete = 0
                        };
                        outboundDetails.Add(outboundDetail);
                        Console.WriteLine($"   📝 准备销售明细: ID={currentDetailId}, DetailId={detail.DetailId}, StorageId={detail.StorageId}, OutboundNum={detail.OneOutboundNum}");
                    }

                    // 批量插入出库明细
                    Console.WriteLine($"🔄 开始批量插入销售出库明细...");
                    try
                    {
                        var insertDetailsResult = await _dbContext.Insertable(outboundDetails).ExecuteCommandAsync();
                        Console.WriteLine($"✅ 销售出库明细创建成功，数量: {insertDetailsResult}");
                    }
                    catch (Exception detailEx)
                    {
                        Console.WriteLine($"❌ 插入销售出库明细异常: {detailEx.Message}");
                        Console.WriteLine($"异常详情: {detailEx.StackTrace}");
                        throw new Exception($"插入销售出库明细失败: {detailEx.Message}", detailEx);
                    }

                    // 3. 更新货品表的当前库存（减少库存）
                    // 需要通过销售订单明细ID找到对应的货品ID
                    foreach (var detail in request.Details)
                    {
                        // 先查询销售订单明细，获取对应的货品ID
                        Console.WriteLine($"🔍 查询销售订单明细，DetailId: {detail.DetailId}");
                        var salesDetail = await _dbContext.Queryable<shoporderdetailsmodel>()
                            .Where(s => s.Id == detail.DetailId)
                            .FirstAsync();

                        if (salesDetail == null)
                        {
                            throw new Exception($"未找到销售订单明细，明细ID: {detail.DetailId}");
                        }
                        Console.WriteLine($"✅ 找到销售订单明细，GoodsId: {salesDetail.GoodsId}");

                        // 查询当前库存
                        var currentProduct = await _dbContext.Queryable<productinformationmodel>()
                            .Where(p => p.Id == salesDetail.GoodsId)
                            .FirstAsync();
                        
                        if (currentProduct == null)
                        {
                            throw new Exception($"未找到货品信息，货品ID: {salesDetail.GoodsId}");
                        }
                        Console.WriteLine($"📦 当前库存信息 - 货品ID: {salesDetail.GoodsId}, 当前库存: {currentProduct.currentInventory}, 准备减少: {detail.OneOutboundNum}");

                        // 检查库存是否充足
                        if (currentProduct.currentInventory < detail.OneOutboundNum)
                        {
                            throw new Exception($"库存不足，货品ID: {salesDetail.GoodsId}, 当前库存: {currentProduct.currentInventory}, 需要出库: {detail.OneOutboundNum}");
                        }

                        // 更新货品库存
                        var updateResult = await _dbContext.Updateable<productinformationmodel>()
                            .SetColumns(p => p.currentInventory == p.currentInventory - detail.OneOutboundNum)
                            .Where(p => p.Id == salesDetail.GoodsId)
                            .ExecuteCommandAsync();

                        if (updateResult <= 0)
                        {
                            throw new Exception($"更新货品库存失败，货品ID: {salesDetail.GoodsId}，影响行数: {updateResult}");
                        }
                        Console.WriteLine($"✅ 货品库存更新成功，货品ID: {salesDetail.GoodsId}, 减少数量: {detail.OneOutboundNum}, 影响行数: {updateResult}");
                    }

                    // 4. 更新销售订单明细表的ShopOrderNum（减少可出库数量）
                    foreach (var detail in request.Details)
                    {
                        // 查询当前销售订单明细信息
                        var currentSales = await _dbContext.Queryable<shoporderdetailsmodel>()
                            .Where(s => s.Id == detail.DetailId)
                            .FirstAsync();
                        
                        if (currentSales == null)
                        {
                            throw new Exception($"未找到销售订单明细，明细ID: {detail.DetailId}");
                        }
                        Console.WriteLine($"📋 当前销售订单明细信息 - 明细ID: {detail.DetailId}, 当前订单数量: {currentSales.ShopOrderNum}, 准备减少: {detail.OneOutboundNum}");

                        // 检查订单数量是否充足
                        if (currentSales.ShopOrderNum < detail.OneOutboundNum)
                        {
                            throw new Exception($"销售订单数量不足，明细ID: {detail.DetailId}, 当前订单数量: {currentSales.ShopOrderNum}, 需要出库: {detail.OneOutboundNum}");
                        }

                        var updateResult = await _dbContext.Updateable<shoporderdetailsmodel>()
                            .SetColumns(s => s.ShopOrderNum == s.ShopOrderNum - detail.OneOutboundNum)
                            .Where(s => s.Id == detail.DetailId)
                            .ExecuteCommandAsync();

                        if (updateResult <= 0)
                        {
                            throw new Exception($"更新销售订单明细失败，明细ID: {detail.DetailId}，影响行数: {updateResult}");
                        }
                        Console.WriteLine($"✅ 销售订单明细更新成功，明细ID: {detail.DetailId}, 减少数量: {detail.OneOutboundNum}, 影响行数: {updateResult}");
                    }

                    Console.WriteLine("✅ 销售订单出库事务操作全部完成");
                    finalOutboundId = outboundId; // 将ID赋值给外部变量
                });

                Console.WriteLine($"✅ 销售订单出库事务提交成功，出库单ID: {finalOutboundId}");
                return finalOutboundId;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 销售订单出库事务操作失败: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw;
            }
        }

        /// <summary>
        /// 分页查询出库单（多表联查）
        /// </summary>
        /// <param name="pageIndex">页码（从0开始）</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="outboundCode">出库编号（可选）</param>
        /// <param name="userId">用户ID（可选）</param>
        /// <returns>分页结果</returns>
        public async Task<(List<OutboundDto> Data, int TotalCount)> GetPageListWithRelationsAsync(
            int pageIndex,
            int pageSize,
            string outboundCode = null,
            long? userId = null)
        {
            try
            {
                // 构建查询条件
                var query = _dbContext.Queryable<outboundmodel, inouttypemodel, warehousemodel, Sys_User>(
                    (o, iot, w, u) => new JoinQueryInfos(
                        JoinType.Left, o.OutInTypeId == iot.InOutTypeId,
                        JoinType.Left, o.WarehouseId == w.Id,
                        JoinType.Left, o.UserId == u.User_Id
                    ))
                    .Where((o, iot, w, u) => o.IsDelete == 0); // 只查询未删除的记录

                // 添加出库编号查询条件
                if (!string.IsNullOrEmpty(outboundCode))
                {
                    query = query.Where((o, iot, w, u) => o.OutboundCode.Contains(outboundCode));
                }

                // 添加用户ID查询条件
                if (userId.HasValue)
                {
                    query = query.Where((o, iot, w, u) => o.UserId == userId.Value);
                }

                // 执行分页查询
                RefAsync<int> totalCount = 0;
                var result = await query
                    .OrderByDescending((o, iot, w, u) => o.CreateDate) // 按创建时间倒序排列
                    .Select((o, iot, w, u) => new OutboundDto
                    {
                        Id = o.Id,
                        OutboundCode = o.OutboundCode,
                        OutInTypeId = o.OutInTypeId,
                        InOutTypeName = iot.InOutTypeName ?? "", // 出入库类型名称
                        WarehouseId = o.WarehouseId,
                        WarehouseName = w.WarehouseName ?? "", // 仓库名称
                        OutboundDate = o.OutboundDate,
                        RelationalID = o.RelationalID,
                        Contact = o.Contact,
                        Consignee = o.Consignee,
                        ContactPhone = o.ContactPhone,
                        UserId = o.UserId,
                        UserName = u.UserName ?? "", // 用户名称
                        Remark = o.Remark,
                        Status = o.Status,
                        OutboundAllNum = o.OutboundAllNum,
                        OutboundAllMoney = o.OutboundAllMoney,
                        OutboundAllGoods = o.OutboundAllGoods,
                        Creator = o.Creator,
                        CreateDate = o.CreateDate,
                        Modifier = o.Modifier,
                        ModifyDate = o.ModifyDate
                    })
                    .ToPageListAsync(pageIndex, pageSize, totalCount);

                return (result, totalCount.Value);
            }
            catch (Exception ex)
            {
                // 记录异常日志
                Console.WriteLine($"❌ OutboundRepository.GetPageListWithRelationsAsync异常: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw;
            }
        }

        /// <summary>
        /// 根据出库ID查询出库明细列表（多表联查）
        /// </summary>
        /// <param name="outboundId">出库单ID</param>
        /// <returns>出库明细列表</returns>
        public async Task<List<OutboundDetailDto>> GetOutboundDetailsByIdAsync(long outboundId)
        {
            try
            {
                Console.WriteLine($"🔍 开始查询出库明细，出库单ID: {outboundId}");

                // 先查询出库单信息，确定出库类型
                var outbound = await _dbContext.Queryable<outboundmodel>()
                    .Where(o => o.Id == outboundId && o.IsDelete == 0)
                    .FirstAsync();

                if (outbound == null)
                {
                    Console.WriteLine($"❌ 未找到出库单，ID: {outboundId}");
                    return new List<OutboundDetailDto>();
                }

                Console.WriteLine($"✅ 找到出库单，出库类型ID: {outbound.OutInTypeId}");

                var result = new List<OutboundDetailDto>();

                // 根据出库类型判断是销售出库还是退货出库
                if (outbound.OutInTypeId == 16) // 销售出库
                {
                    Console.WriteLine("📦 处理销售出库明细");
                    result = await GetSalesOutboundDetailsAsync(outboundId);
                }
                else if (outbound.OutInTypeId == 14) // 退货出库
                {
                    Console.WriteLine("📦 处理退货出库明细");
                    result = await GetReturnOutboundDetailsAsync(outboundId);
                }
                else
                {
                    Console.WriteLine($"⚠️ 未知的出库类型: {outbound.OutInTypeId}");
                    // 对于其他类型的出库，只查询基本明细信息
                    result = await GetBasicOutboundDetailsAsync(outboundId);
                }

                Console.WriteLine($"✅ 查询完成，返回明细数量: {result.Count}");
                return result;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ OutboundRepository.GetOutboundDetailsByIdAsync异常: {ex.Message}");
                Console.WriteLine($"异常堆栈: {ex.StackTrace}");
                throw;
            }
        }

        /// <summary>
        /// 获取销售出库明细（与销售订单明细和货品表联查）
        /// </summary>
        /// <param name="outboundId">出库单ID</param>
        /// <returns>出库明细列表</returns>
        private async Task<List<OutboundDetailDto>> GetSalesOutboundDetailsAsync(long outboundId)
        {
            try
            {
                var result = await _dbContext.Queryable<outbounddetailmodel, shoporderdetailsmodel, productinformationmodel>(
                    (od, sod, p) => new JoinQueryInfos(
                        JoinType.Left, od.DetialId == sod.Id,
                        JoinType.Left, sod.GoodsId == p.Id
                    ))
                    .Where((od, sod, p) => od.OutboundId == outboundId && od.IsDelete == 0)
                    .OrderBy((od, sod, p) => od.Id)
                    .Select((od, sod, p) => new OutboundDetailDto
                    {
                        Id = od.Id,
                        OutboundId = od.OutboundId,
                        DetialId = od.DetialId,
                        StorageId = od.StorageId,
                        OneOutboundNum = od.OneOutboundNum,
                        BatchNumber = od.BatchNumber ?? "",
                        ProductionDate = od.ProductionDate,
                        Remark = od.Remark ?? "",
                        GoodsId = sod.GoodsId,
                        GoodsCode = p.goodsCode ?? "",
                        GoodsName = p.goodsName ?? "",
                        SpecModel = p.specModel ?? "",
                        CurrentInventory = p.currentInventory,
                        Status = p.status,
                        ProductTypeId = p.productTypeId,
                        SupplierId = p.supplierId,
                        Creator = od.Creator ?? "",
                        CreateDate = od.CreateDate
                    })
                    .ToListAsync();

                Console.WriteLine($"✅ 销售出库明细查询完成，数量: {result.Count}");
                return result;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 查询销售出库明细异常: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 获取退货出库明细（与退货订单明细、进货订单明细和货品表联查）
        /// </summary>
        /// <param name="outboundId">出库单ID</param>
        /// <returns>出库明细列表</returns>
        private async Task<List<OutboundDetailDto>> GetReturnOutboundDetailsAsync(long outboundId)
        {
            try
            {
                var result = await _dbContext.Queryable<outbounddetailmodel, returnorderdetailsmodel, PurchaseOrderItemModel, productinformationmodel>(
                    (od, rod, poi, p) => new JoinQueryInfos(
                        JoinType.Left, od.DetialId == rod.Id,
                        JoinType.Left, rod.PurchaseOrderItemId == poi.Id,
                        JoinType.Left, poi.GoodsId == p.Id
                    ))
                    .Where((od, rod, poi, p) => od.OutboundId == outboundId && od.IsDelete == 0)
                    .OrderBy((od, rod, poi, p) => od.Id)
                    .Select((od, rod, poi, p) => new OutboundDetailDto
                    {
                        Id = od.Id,
                        OutboundId = od.OutboundId,
                        DetialId = od.DetialId,
                        StorageId = od.StorageId,
                        OneOutboundNum = od.OneOutboundNum,
                        BatchNumber = od.BatchNumber ?? "",
                        ProductionDate = od.ProductionDate,
                        Remark = od.Remark ?? "",
                        GoodsId = poi.GoodsId,
                        GoodsCode = p.goodsCode ?? "",
                        GoodsName = p.goodsName ?? "",
                        SpecModel = p.specModel ?? "",
                        CurrentInventory = p.currentInventory,
                        Status = p.status,
                        ProductTypeId = p.productTypeId,
                        SupplierId = p.supplierId,
                        Creator = od.Creator ?? "",
                        CreateDate = od.CreateDate
                    })
                    .ToListAsync();

                Console.WriteLine($"✅ 退货出库明细查询完成，数量: {result.Count}");
                return result;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 查询退货出库明细异常: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 获取基本出库明细（只查询出库明细表信息）
        /// </summary>
        /// <param name="outboundId">出库单ID</param>
        /// <returns>出库明细列表</returns>
        private async Task<List<OutboundDetailDto>> GetBasicOutboundDetailsAsync(long outboundId)
        {
            try
            {
                var result = await _dbContext.Queryable<outbounddetailmodel>()
                    .Where(od => od.OutboundId == outboundId && od.IsDelete == 0)
                    .OrderBy(od => od.Id)
                    .Select(od => new OutboundDetailDto
                    {
                        Id = od.Id,
                        OutboundId = od.OutboundId,
                        DetialId = od.DetialId,
                        StorageId = od.StorageId,
                        OneOutboundNum = od.OneOutboundNum,
                        BatchNumber = od.BatchNumber ?? "",
                        ProductionDate = od.ProductionDate,
                        Remark = od.Remark ?? "",
                        GoodsId = 0, // 无法获取货品信息
                        GoodsCode = "",
                        GoodsName = "",
                        SpecModel = "",
                        CurrentInventory = 0,
                        Status = 0,
                        ProductTypeId = 0,
                        SupplierId = 0,
                        Creator = od.Creator ?? "",
                        CreateDate = od.CreateDate
                    })
                    .ToListAsync();

                Console.WriteLine($"✅ 基本出库明细查询完成，数量: {result.Count}");
                return result;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ 查询基本出库明细异常: {ex.Message}");
                throw;
            }
        }
    }
}
